package com.stt.mapper;

import com.stt.entity.Grade;
import org.apache.ibatis.annotations.*;

@Mapper
public interface GradeMapper {

    @Insert("INSERT INTO grades(user_id, course_id, grade) VALUES(#{userId}, #{courseId}, #{grade})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Grade grade);

    @Delete("DELETE FROM grades WHERE id = #{id}")
    int deleteById(Integer id);

    @Update("UPDATE grades SET grade=#{grade} WHERE id=#{id}")
    int update(Grade grade);

    // 根据id查询关联的学生和课程信息
    @Select("SELECT g.*, u.username as student_name, c.name as course_name " +
            "FROM grades g " +
            "LEFT JOIN users u ON g.user_id = u.id " +
            "LEFT JOIN courses c ON g.course_id = c.id " +
            "WHERE g.id = #{id}")
    @Results({
        @Result(id = true, property = "id", column = "id"),
        @Result(property = "userId", column = "user_id"),
        @Result(property = "courseId", column = "course_id"),
        @Result(property = "grade", column = "grade"),
        @Result(property = "student", column = "user_id",
                one = @One(select = "com.stt.mapper.UserMapper.findById")),
        @Result(property = "course", column = "course_id",
                one = @One(select = "com.stt.mapper.CourseMapper.findById"))
    })
    Grade findByIdWithDetails(Integer id);
    // 根据id查询成绩及关联的学生和课程信息
    @Select("SELECT g.*, u.username as student_name, c.name as course_name " +
            "FROM grades g " +
            "LEFT JOIN users u ON g.user_id = u.id " +
            "LEFT JOIN courses c ON g.course_id = c.id " +
            "WHERE g.user_id = #{userId} AND g.course_id = #{courseId}")
    @Results({
            @Result(id = true, property = "id", column = "id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "courseId", column = "course_id"),
            @Result(property = "grade", column = "grade"),
            @Result(property = "student", column = "user_id",
                    one = @One(select = "com.stt.mapper.UserMapper.findById")),
            @Result(property = "course", column = "course_id",
                    one = @One(select = "com.stt.mapper.CourseMapper.findById"))
    })
    Grade findByUserIdAndCourseId(@Param("userId") Integer userId, @Param("courseId") Integer courseId);
}
